package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * Class to hold some SQL Query that is used in multiple places.
 * 
 * It provides access to two Singletons, CatAndPoints and QuestionManager.
 */
public class SQLCall
{
	private static CatAndPoints cap = null;
	private static QuestionManager qManager = null;
	/**
	 * Get the next value to the sequence passed in
	 * 
	 * @param conn
	 *            The database connection
	 * @param sequence
	 *            The name of the sequence
	 * @return The next value in the sequence
	 */
	public static int getNextSeq(Connection conn, String sequence) throws SQLException
	{
		int index = -1;
		
		String query = "SELECT " + sequence + ".nextval from dual";
		PreparedStatement pst = conn.prepareStatement(query);
		ResultSet rset = pst.executeQuery();
		rset.next();
		index = rset.getInt(1);
		pst.close();
	
		return index;
	}

	/**
	 * Grab a list of all the question type that question should be generated
	 * for.
	 * 
	 * @param conn
	 *            A database connection
	 * @return CatAndPoints object of all current type(category) of question available
	 */
	public static CatAndPoints getCategoryList(Connection conn) throws SQLException
	{
		// If an instance already exists, return it.
		if(cap != null)
			return cap;

		cap = new CatAndPoints();
		Statement quesType = conn.createStatement();
		ResultSet rset = quesType
				.executeQuery("SELECT type_code, points, type_description FROM QUESTION_TYPE");
		while (rset.next())
		{
			cap.add(rset.getInt("type_code"), rset.getInt("points"), 
					rset.getString("type_description"));
		}
		quesType.close();
		rset.close();
		
		return cap;
	}
	
	/**
	 * Constructs a QuestionManager and fills it with required data.
	 * 
	 * @param conn A connection to the database.
	 * @return The question manager which handles the questions of an exam.
	 * @throws SQLException 
	 */
	public static QuestionManager getQuestionManager(Connection conn) 
	throws SQLException
	{
		// If an instance already exists, return it.
		if(qManager != null)
			return qManager;
		
		qManager = new QuestionManager();
		
		// Make sure that cap is not null;
		getCategoryList(conn);
		
		for(int cat : cap) {
			PreparedStatement quesType = conn.prepareStatement(
					"SELECT question_id, points "
					+ "FROM QUESTION_CONTENT INNER JOIN QUESTION "
					+ "ON QUESTION.question_content_id = "
					+ "QUESTION_CONTENT.question_content_id "
					+ "WHERE question_type_code = ?");
			quesType.setInt(1, cat);
			ResultSet r = quesType.executeQuery();
			
			try
			{
				while (r.next())
				{
					// Add details to the questionManager
					qManager.add(new QuestionDetails(cat,
							r.getInt("question_id"), r.getInt("points")));
				}
			} catch (Exception e)
			{
				e.printStackTrace();
			}
			
			r.close();// close the ResultSet
			quesType.close();// close Statement
		}
	
		qManager.finish();
		return qManager;
	}
	
	private static ArrayList<Integer> getUsers(Connection conn, String id,
			String table)
	{
		ArrayList<Integer> userList = new ArrayList<Integer>();
		ResultSet user;
		try
		{
			PreparedStatement pst = conn.prepareStatement("SELECT " + id
					+ " FROM " + table);
			user = pst.executeQuery();

			while (user.next())
			{
				userList.add(user.getInt(id));
			}

			user.close();
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

		return userList;
	}

	public static ArrayList<Integer> getApeUsers(Connection conn)
	{
		return getUsers(conn, "user_id", "ape_user");
	}

	public static ArrayList<Integer> getStudentUsers(Connection conn)
	{
		return getUsers(conn, "student_id", "student_user");
	}

	public static boolean isFound(Connection conn, String what, String from)
	{
		try
		{
			int index = 0;
			String query = "SELECT COUNT(" + what + ") FROM " + from;
			PreparedStatement pst = conn.prepareStatement(query);
			ResultSet rset = pst.executeQuery();
			rset.next();
			index = rset.getInt(1);
			pst.close();

			if (index > 0)
				return true;
			else
				return false;
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

		return false;
	}

	public static int getCategoryPoints(Connection conn, int cat)
	{
		ResultSet response;
		int ans = -1;

		try
		{
			PreparedStatement pst = conn.prepareStatement("SELECT points "
					+ " FROM question_type WHERE type_code = " + cat);
			response = pst.executeQuery();
			response.next();
			ans = response.getInt("points");

			response.close();
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

		return ans;
	}

	public static int getQuestionPoints(Connection conn, int q, int v)
	{
		ResultSet response;
		int ans = -1;

		try
		{
			PreparedStatement pst = conn
					.prepareStatement("SELECT points "
							+ "FROM question INNER JOIN question_content "
							+ "ON question.question_content_id = question_content.question_content_id "
							+ "AND question_id = " + q + " AND q_version = "
							+ v);
			response = pst.executeQuery();

			response.next();
			ans = response.getInt("points");

			response.close();
			pst.close();
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

		return ans;
	}
}
